﻿namespace HuaWei.CommClass
{
    using System;

    public static class CRepZFJKSQL
    {
        public static string REP00 = "\r\ndelete from REP_ZQJKZF1 where bdid='{0}' and qcid={1}\r\n";
        public static string REP10 = @" insert into REP_ZQJKZF1 (id,bdid,qcid,jllx,bz100,bz200,bz300,bz400,bz500,bz600,bz700,bzzlj,zxzlj) 
                                        select newid() as id,'{0}' as bdid,'{1}' as qcid,jllx,sum(bz100) as bz100,sum(bz200) as bz200,sum(bz300) as bz300,sum(bz400) as bz400, sum(bz500) as bz500,sum(bz600) as bz600,sum(bz700) as bz700,sum(bzzlj) as bzzlj,sum(zxzlj) as zxzlj  
                                        from (  select  0 as jllx,qdbh, 
				                                        case when substring(qdbh,1,1)='1' then htje else 0 end as bz100, 
				                                        case when substring(qdbh,1,1)='2' then htje else 0 end as bz200, 
				                                        case when substring(qdbh,1,1)='3' then htje else 0 end as bz300, 
				                                        case when substring(qdbh,1,1)='4' then htje else 0 end as bz400, 
				                                        case when substring(qdbh,1,1)='5' then htje else 0 end as bz500, 
				                                        case when substring(qdbh,1,1)='6' then htje else 0 end as bz600, 
				                                        case when substring(qdbh,1,1)='7' then htje else 0 end as bz700, 
				                                        case when qdlx='2' then htje else 0 end as bzzlj,  
				                                        case when qdlx='3' then htje else 0 end as zxzlj  from jlqd where bdid='{0}' and cjm<>0 union all   
		                                        select  1 as jllx,qdbh, 
				                                        case when substring(qdbh,1,1)='1' then sgtje else 0 end as bz100, 
				                                        case when substring(qdbh,1,1)='2' then sgtje else 0 end as bz200, 
				                                        case when substring(qdbh,1,1)='3' then sgtje else 0 end as bz300, 
				                                        case when substring(qdbh,1,1)='4' then sgtje else 0 end as bz400, 
				                                        case when substring(qdbh,1,1)='5' then sgtje else 0 end as bz500, 
				                                        case when substring(qdbh,1,1)='6' then sgtje else 0 end as bz600, 
				                                        case when substring(qdbh,1,1)='7' then sgtje else 0 end as bz700, 
				                                        case when qdlx='2' then sgtje else 0 end as bzzlj, 
				                                        case when qdlx='3' then sgtje else 0 end as zxzlj  from jlqd where sgtyx=1 and bdid='{0}' and cjm<>0 union all 
		                                        select  2 as jllx,qdbh, 
				                                        case when substring(qdbh,1,1)='1' then bgzjje else 0 end as bz100, 
				                                        case when substring(qdbh,1,1)='2' then bgzjje else 0 end as bz200, 
				                                        case when substring(qdbh,1,1)='3' then bgzjje else 0 end as bz300, 
				                                        case when substring(qdbh,1,1)='4' then bgzjje else 0 end as bz400, 
				                                        case when substring(qdbh,1,1)='5' then bgzjje else 0 end as bz500, 
				                                        case when substring(qdbh,1,1)='6' then bgzjje else 0 end as bz600, 
				                                        case when substring(qdbh,1,1)='7' then bgzjje else 0 end as bz700, 
				                                        0 as bzzlj, 
				                                        0 as zxzlj   from BGLQD  where bdid='{0}' union all 
		                                        select  3 as jllx,qdbh, 
				                                        case when substring(qdbh,1,1)='1' then (case when sgtyx=1 then sgtje else htje end) else 0 end as bz100, 
				                                        case when substring(qdbh,1,1)='2' then (case when sgtyx=1 then sgtje else htje end) else 0 end as bz200, 
				                                        case when substring(qdbh,1,1)='3' then (case when sgtyx=1 then sgtje else htje end) else 0 end as bz300, 
				                                        case when substring(qdbh,1,1)='4' then (case when sgtyx=1 then sgtje else htje end) else 0 end as bz400, 
				                                        case when substring(qdbh,1,1)='5' then (case when sgtyx=1 then sgtje else htje end) else 0 end as bz500, 
				                                        case when substring(qdbh,1,1)='6' then (case when sgtyx=1 then sgtje else htje end) else 0 end as bz600, 
				                                        case when substring(qdbh,1,1)='7' then (case when sgtyx=1 then sgtje else htje end) else 0 end as bz700, 
				                                        case when qdlx='2' then (case when sgtyx=1 then sgtje else htje end) else 0 end as bzzlj,  
				                                        case when qdlx='3' then (case when sgtyx=1 then sgtje else htje end) else 0 end as zxzlj   from jlqd where bdid='{0}' and cjm<>0 union all 
		                                        select  3 as jllx,qdbh, 
				                                        case when substring(qdbh,1,1)='1' then bgzjje else 0 end as bz100, 
				                                        case when substring(qdbh,1,1)='2' then bgzjje else 0 end as bz200, 
				                                        case when substring(qdbh,1,1)='3' then bgzjje else 0 end as bz300, 
				                                        case when substring(qdbh,1,1)='4' then bgzjje else 0 end as bz400, 
				                                        case when substring(qdbh,1,1)='5' then bgzjje else 0 end as bz500, 
				                                        case when substring(qdbh,1,1)='6' then bgzjje else 0 end as bz600, 
				                                        case when substring(qdbh,1,1)='7' then bgzjje else 0 end as bz700, 
				                                        0 as bzzlj,  
				                                        0 as zxzlj   from BGLQD  where bdid='{0}'  union all 
		                                        select  6 as jllx,a.qdbh,
				                                        case when substring(a.qdbh,1,1)='1' then round(bqjls*b.zbdj,0) else 0 end as bz100,
				                                        case when substring(a.qdbh,1,1)='2' then round(bqjls*b.zbdj,0) else 0 end as bz200,
				                                        case when substring(a.qdbh,1,1)='3' then round(bqjls*b.zbdj,0) else 0 end as bz300,
				                                        case when substring(a.qdbh,1,1)='4' then round(bqjls*b.zbdj,0) else 0 end as bz400,
				                                        case when substring(a.qdbh,1,1)='5' then round(bqjls*b.zbdj,0) else 0 end as bz500,
				                                        case when substring(a.qdbh,1,1)='6' then round(bqjls*b.zbdj,0) else 0 end as bz600,
				                                        case when substring(a.qdbh,1,1)='7' then round(bqjls*b.zbdj,0) else 0 end as bz700,
				                                        case when a.qdbh=b.qdbh and b.qdlx=2 then round(bqjls*b.zbdj,0) else 0 end as bzzlj,
				                                        case when a.qdbh=b.qdbh and b.qdlx=3 then round(bqjls*b.zbdj,0) else 0 end as zxzlj 
		                                         from  (select BDID, qdbh,sum([bqjls]) as bqjls from qdjl_mx where bdid='{0}' and qcid<={1} group by bdid,qdbh) as a 
		                                         left join  jlqd as b on a.BDID=b.BDID and a.qdbh=b.qdbh  union all 
		                                         select 7 as jllx,a.qdbh, 
				                                        case when substring(a.qdbh,1,1)='1' then round(bqjls*b.dj,0) else 0 end as bz100, 
				                                        case when substring(a.qdbh,1,1)='2' then round(bqjls*b.dj,0) else 0 end as bz200, 
				                                        case when substring(a.qdbh,1,1)='3' then round(bqjls*b.dj,0) else 0 end as bz300, 
				                                        case when substring(a.qdbh,1,1)='4' then round(bqjls*b.dj,0) else 0 end as bz400, 
				                                        case when substring(a.qdbh,1,1)='5' then round(bqjls*b.dj,0) else 0 end as bz500, 
				                                        case when substring(a.qdbh,1,1)='6' then round(bqjls*b.dj,0) else 0 end as bz600, 
				                                        case when substring(a.qdbh,1,1)='7' then round(bqjls*b.dj,0) else 0 end as bz700, 
				                                        0 as bzzlj, 
				                                        0 as zxzlj  
		                                         from   (select BDID, qdbh,sum([bqjls]) as bqjls from BGQDJL_MX where bdid='{0}' and qcid<={1} group by bdid,qdbh) as a 
		                                         left join  BGLQD as b on a.BDID=b.BDID and a.qdbh=b.qdbh) aa group by aa.jllx";
        public static string REP12 = @" insert into REP_ZQJKZF1 (id,bdid,qcid,jllx,bz100,bz200,bz300,bz400,bz500,bz600,bz700,bzzlj,zxzlj) 
                                        select  newid() as id,
		                                        '{0}' as bdid,
		                                        '{1}' as qcid,
		                                        4 as jllx,
		                                        sum(bz100) as bz100,
		                                        sum(bz200) as bz200,
		                                        sum(bz300) as bz300,
		                                        sum(bz400) as bz400, 
		                                        sum(bz500) as bz500,
		                                        sum(bz600) as bz600,
		                                        sum(bz700) as bz700,
		                                        sum(bzzlj) as bzzlj,
		                                        sum(zxzlj) as zxzlj  
                                        from (  select id,bdid,qcid,jllx,bz100,bz200,bz300,bz400,bz500,bz600,bz700,bzzlj,zxzlj from REP_ZQJKZF1  where bdid='{0}' and jllx=6 and qcid={1}  
                                        union all  select id,bdid,qcid,jllx,0-bz100 as bz100,0-bz200 as bz200,0-bz300 as bz300,0-bz400 as bz400,0-bz500 as bz500,0-bz600 as bz600,0-bz700 as bz700,0-bzzlj as bzzlj,0-zxzlj as zxzlj  from REP_ZQJKZF1  where bdid='{0}' and jllx=6 and qcid={1}-1  ) aa group by bdid ";
        public static string REP13 = @" insert into REP_ZQJKZF1 (id,bdid,qcid,jllx,bz100,bz200,bz300,bz400,bz500,bz600,bz700,bzzlj,zxzlj) 
                                        select  newid() as id,
		                                        '{0}' as bdid,
		                                        '{1}' as qcid,
		                                        5 as jllx,
		                                        sum(bz100) as bz100,
		                                        sum(bz200) as bz200,
		                                        sum(bz300) as bz300,
		                                        sum(bz400) as bz400, 
		                                        sum(bz500) as bz500,
		                                        sum(bz600) as bz600,
		                                        sum(bz700) as bz700,
		                                        sum(bzzlj) as bzzlj,
		                                        sum(zxzlj) as zxzlj  
                                        from (  select id,bdid,qcid,jllx,bz100,bz200,bz300,bz400,bz500,bz600,bz700,bzzlj,zxzlj from REP_ZQJKZF1  where bdid='{0}' and jllx=7 and qcid={1}  
                                        union all  select id,bdid,qcid,jllx,0-bz100 as bz100,0-bz200 as bz200,0-bz300 as bz300,0-bz400 as bz400,0-bz500 as bz500,0-bz600 as bz600,0-bz700 as bz700,0-bzzlj as bzzlj,0-zxzlj as zxzlj  from REP_ZQJKZF1  where bdid='{0}' and jllx=7 and qcid={1}-1  ) aa group by bdid ";
        public static string REP11 = "\r\ninsert into REP_ZQJKZF1 (id,bdid,qcid,jllx,bz100,bz200,bz300,bz400,bz500,bz600,bz700,bzzlj,zxzlj)\r\nselect newid() as id,'{0}' as bdid,'{1}' as qcid,8 as jllx,sum(bz100) as bz100,sum(bz200) as bz200,sum(bz300) as bz300,sum(bz400) as bz400,\r\nsum(bz500) as bz500,sum(bz600) as bz600,sum(bz700) as bz700,sum(bzzlj) as bzzlj,sum(zxzlj) as zxzlj\r\n from ( \r\nselect * from REP_ZQJKZF1  where bdid='{0}' and jllx=6 and qcid='{1}' \r\nunion all \r\nselect * from REP_ZQJKZF1  where bdid='{0}' and jllx=7 and qcid='{1}'  \r\n) aa group by bdid\r\n";
        public static string REP14 = "\r\ninsert into REP_ZQJKZF1 (id,bdid,qcid,jllx,bz100,bz200,bz300,bz400,bz500,bz600,bz700,bzzlj,zxzlj)\r\nselect newid() as id,'{0}' as bdid,'{1}' as qcid,9 as jllx,sum(bz100) as bz100,sum(bz200) as bz200,sum(bz300) as bz300,sum(bz400) as bz400,\r\nsum(bz500) as bz500,sum(bz600) as bz600,sum(bz700) as bz700,sum(bzzlj) as bzzlj,sum(zxzlj) as zxzlj\r\n from ( \r\nselect * from REP_ZQJKZF1  where bdid='{0}' and jllx=4 and qcid='{1}' \r\nunion all \r\nselect * from REP_ZQJKZF1  where bdid='{0}' and jllx=5 and qcid='{1}'  \r\n) aa group by bdid\r\n";
        public static string REP15 = "\r\nupdate REP_ZQJKZF1 set hj = bz100 + bz200 + bz300 + bz400 + bz500 + bz600 + bz700 + bzzlj + zxzlj where bdid='{0}' and qcid={1}\r\n";
        public static string REP20 = "\r\ndelete from REP_ZQJKZF2 where bdid='{0}' and qcid={1} \r\n";
        public static string REP21_0 = "\r\ninsert into REP_ZQJKZF2 (id,bdid,qcid,JLLX,BQKCDYYFK,KHJG,KSJ,KBLJ,KHGZ,WYFJ,KKHJ,SJZFJE)\r\nselect newid() as id,'{0}' as bdid,'{1}' as qcid,0 as JLLX,sum(BQKCDYYFK) as BQKCDYYFK,sum(KHJG) as KHJG,sum(KSJ) as KSJ,sum(KBLJ) as KBLJ,sum(KHGZ) as KHGZ,sum(WYFJ) as WYFJ,sum(KKHJ) as KKHJ,sum(SJZFJE) as SJZFJE from (\r\nselect 0 as jllx,BQKCDYYFK,0 as KHJG,round({2}*0.0333,0) as KSJ,0 as KBLJ,0 as KHGZ,0 as WYFJ,0 as KKHJ,0 as SJZFJE from qtjl_dyyfk  where bdid='{0}' and qcid={1}\r\nunion all \r\nselect 0 as jllx,0 as BQKCDYYFK,KHJG,0 as KSJ,0 as KBLJ,KHGZ,0 as WYFJ,0 as KKHJ,0 as SJZFJE from qtjl_qtkk where bdid='{0}' and qcid={1}\r\nunion all\r\nselect 0 as jllx,0 as BQKCDYYFK,0 as KHJG,0 as KSJ,0 as KBLJ,0 as KHGZ,WYFJ,0 as KKHJ,0 as SJZFJE from qtjl_wyfj  where bdid='{0}' and qcid={1}\r\n) aa group by aa.jllx\r\n";
        public static string REP21_01 = "\r\ninsert into REP_ZQJKZF2 (id,bdid,qcid,JLLX,BQKCDYYFK,KHJG,KSJ,KBLJ,KHGZ,WYFJ,KKHJ,SJZFJE)\r\nselect newid() as id,'{0}' as bdid,'{1}' as qcid,0 as JLLX,sum(BQKCDYYFK) as BQKCDYYFK,sum(KHJG) as KHJG,sum(KSJ) as KSJ,sum(KBLJ) as KBLJ,sum(KHGZ) as KHGZ,sum(WYFJ) as WYFJ,sum(KKHJ) as KKHJ,sum(SJZFJE) as SJZFJE from (\r\nselect 0 as jllx,BQKCDYYFK,0 as KHJG,0 as KSJ,0 as KBLJ,0 as KHGZ,0 as WYFJ,0 as KKHJ,0 as SJZFJE from qtjl_dyyfk  where bdid='{0}' and qcid={1}\r\nunion all \r\nselect 0 as jllx,0 as BQKCDYYFK,KHJG,0 as KSJ,0 as KBLJ,KHGZ,0 as WYFJ,0 as KKHJ,0 as SJZFJE from qtjl_qtkk where bdid='{0}' and qcid={1}\r\nunion all\r\nselect 0 as jllx,0 as BQKCDYYFK,0 as KHJG,0 as KSJ,0 as KBLJ,0 as KHGZ,WYFJ,0 as KKHJ,0 as SJZFJE from qtjl_wyfj  where bdid='{0}' and qcid={1}\r\n) aa group by aa.jllx\r\n";
        public static string REP21_1 = "\r\n update REP_ZQJKZF2 set KBLJ={2}\r\n where JLLX=0 and bdid='{0}' and  qcid={1}\r\n";
        public static string REP21_2 = "\r\n update REP_ZQJKZF2 set KKHJ=BQKCDYYFK+KHJG+KSJ+KBLJ+KHGZ+WYFJ,SJZFJE={2}-round(BQKCDYYFK+KHJG+KSJ+KBLJ+KHGZ+WYFJ,0)\r\n where JLLX=0 and bdid='{0}' and  qcid={1}\r\n";
        public static string REP21_3 = "\r\n update REP_ZQJKZF2 set SJZFJEDX='{2}'\r\n where JLLX=0 and bdid='{0}' and  qcid={1}\r\n";
        public static string REP22 = "\r\ninsert into REP_ZQJKZF2 (id,bdid,qcid,JLLX,BQKCDYYFK,KHJG,KSJ,KBLJ,KHGZ,WYFJ,KKHJ,SJZFJE)\r\nselect newid() as id,'{0}' as bdid,'{1}' as qcid,1 as JLLX,sum(BQKCDYYFK) as BQKCDYYFK,sum(KHJG) as KHJG,sum(KSJ) as KSJ,sum(KBLJ) as KBLJ,sum(KHGZ) as KHGZ,sum(WYFJ) as WYFJ,sum(KKHJ) as KKHJ,sum(SJZFJE) as SJZFJE from (\r\nselect * from REP_ZQJKZF2 where JLLX=0 and bdid='{0}' and qcid={1} \r\nunion all\r\nselect * from REP_ZQJKZF2 where JLLX=1 and bdid='{0}' and  qcid={1}-1\r\n) aa group by aa.bdid\r\n";
        public static string SEARCH10 = "\r\nselect * from REP_ZQJKZF1  where bdid='{0}' and qcid={1} and jllx=0\r\n";
        public static string SEARCH11 = "\r\nselect * from REP_ZQJKZF1  where bdid='{0}' and qcid={1} and jllx=1\r\n";
        public static string SEARCH12 = "\r\nselect * from REP_ZQJKZF1  where bdid='{0}' and qcid={1} and jllx=2\r\n";
        public static string SEARCH13 = "\r\nselect * from REP_ZQJKZF1  where bdid='{0}' and qcid={1} and jllx=3\r\n";
        public static string SEARCH14 = "\r\nselect * from REP_ZQJKZF1  where bdid='{0}' and qcid={1} and jllx=4\r\n";
        public static string SEARCH15 = "\r\nselect * from REP_ZQJKZF1  where bdid='{0}' and qcid={1} and jllx=5\r\n";
        public static string SEARCH16 = "\r\nselect * from REP_ZQJKZF1  where bdid='{0}' and qcid={1} and jllx=6\r\n";
        public static string SEARCH17 = "\r\nselect * from REP_ZQJKZF1  where bdid='{0}' and qcid={1} and jllx=7\r\n";
        public static string SEARCH18 = "\r\nselect * from REP_ZQJKZF1  where bdid='{0}' and qcid={1} and jllx=8\r\n";
        public static string SEARCH19 = "\r\nselect *,'' as LargeNum from REP_ZQJKZF1  where bdid='{0}' and qcid={1} and jllx=9\r\n";
        public static string SEARCH20 = "\r\nselect * from REP_ZQJKZF1  where bdid='{0}' and qcid={1}-1 and jllx=6\r\n";
        public static string SEARCH21 = "\r\nselect * from REP_ZQJKZF1  where bdid='{0}' and qcid={1}-1 and jllx=7\r\n";
        public static string SEARCH22 = "\r\nselect * from REP_ZQJKZF1  where bdid='{0}' and qcid={1}-1 and jllx=8\r\n";
        public static string SEARCH31 = "\r\nselect * from REP_ZQJKZF2  where jllx=0 and bdid='{0}' and qcid={1}\r\n";
        public static string SEARCH32 = "\r\nselect * from REP_ZQJKZF2  where jllx=1 and bdid='{0}' and qcid={1}\r\n";
        public static string SEARCH33 = "\r\nselect * from REP_ZQJKZF2  where jllx=1 and bdid='{0}' and qcid={1}-1\r\n";
    }
}

